use Students
go

create table Stuinfo002
(
	stuNo char(100) not null,
	stuName nvarchar(20) not null,
	stuAge char(3) not null,
	stuAddress nvarchar(20) not null,
	stuSeae int primary key identity(1,1),
	stuSex char(1) check(stuSex in(1,0))
)
go



create table fraction
(
	exanNo int primary key identity(1,1),
	stuNo char(10) not null,
	wittenexam int ,
	labexam int
)

go
insert into Stuinfo002(stuNo,stuName,stuAge,stuAddress,stuSex) values('s2501','张秋利',20,'美国硅谷',1),('s2502','李斯文',18,'湖北武汉',0),
('s2503','马文才',22,'湖南长沙',1),('s2504','欧阳俊雄',21,'湖北武汉',0),
('s2505','梅超风',20,'湖北武汉',1),('s2506','陈旋风',19,'美国硅谷',1),
('s2507','陈风',20,'美国硅谷',0)
insert into fraction(stuNo,wittenexam,labexam) values('s2501',50,70),('s2502',60,86),('s2503',86,85),('s2504',40,80),('s2505',70,90),('s2506',85,90)

select stuNo as 学号 from Stuinfo002
select stuName as 姓名 from Stuinfo002
select stuAge as 年龄 from Stuinfo002
select stuAddress as 地址 from Stuinfo002
select stuSeae as 座位号 from Stuinfo002
select stuSex as 性别 from Stuinfo002

select stuNo,stuAge,stuName from Stuinfo002

select stuNo 学号,wittenexam 笔试,labexam 机试 from fraction
select stuNo as 学号,wittenexam as 笔试,labexam as 机试 from fraction
select 学号=stuNo, 笔试=wittenexam, 机试=labexam from fraction

select stuNo 学号,stuName 姓名, stuAddress 地址 ,stuName+'@'+stuAddress 邮箱 from Stuinfo002

select stuNo 学号,wittenexam 笔试,labexam 机试,labexam+wittenexam 总分  from fraction

select distinct stuAddress from Stuinfo002

select distinct stuAge as 所有年龄 from Stuinfo002

select top 3 * from Stuinfo002

select top 4 stuName,stuSeae from Stuinfo002

select top 50 percent * from Stuinfo002

select * from Stuinfo002 where stuAddress='湖北武汉' and stuAge=20

select * from fraction where labexam>60 and labexam<80 order by labexam desc

select * from Stuinfo002 where stuAddress='湖北武汉' OR stuAddress='湖南长沙'
select * from Stuinfo002 where (stuAddress in('湖北武汉','湖南长沙'))

select * from fraction where wittenexam like '[^7,8,9]%' order by wittenexam asc

select * from Stuinfo002 where stuAge is null or stuAge=''

select * from Stuinfo002 where stuAge like '%'

select * from Stuinfo002 where stuName like '张%'

select * from Stuinfo002 where stuAddress like '湖%'

select * from Stuinfo002 where stuName like '张_'

select * from Stuinfo002 where stuName like '__俊%'

select * from Stuinfo002  order by stuAge desc

select * from Stuinfo002  order by stuAge desc,stuSeae asc

select top 1 * from fraction order by wittenexam desc

select top 1 * from fraction order by wittenexam 
--查询每个地方的学生的平均年龄
select AVG(stuAge)from Stuinfo002 where stuAddress='湖北武汉'
select AVG(stuAge)from Stuinfo002 where stuAddress='湖南长沙'
select AVG(stuAge)from Stuinfo002 where stuAddress='美国硅谷'

--查询男女生的分别的年龄总和
select SUM(stuAge)from Stuinfo002 where stuSex='男'
select SUM(stuAge)from Stuinfo002 where stuSex='女'
--查询每个地方的男女生的平均年龄和年龄的总和
select stuAddress,StuSex,SUM(StuAge)年龄总和,AVG(StuAge)平均年龄 from Stuinfo002 group by stuAddress